﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class OpenLabStatusService
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public static bool Exists(int OpenLabID, DateTime date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from OpenLabStatus");
            strSql.Append(" where OpenLabID=@OpenLabID and date=@date ");
            SqlParameter[] parameters = {
					new SqlParameter("@OpenLabID", SqlDbType.Int,4),
					new SqlParameter("@date", SqlDbType.DateTime)			};
            parameters[0].Value = OpenLabID;
            parameters[1].Value = date;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据主键LabID,日期查询一条信息
        /// </summary>
        /// <param name="OpenLabID"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public static Model.OpenLabStatus SelectOneByPrimaryKey(int OpenLabID, DateTime date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from OpenLabStatus");
            strSql.Append(" where OpenLabID=@OpenLabID and date=@date ");
            SqlParameter[] parameters = {
					new SqlParameter("@OpenLabID", SqlDbType.Int,4),
					new SqlParameter("@date", SqlDbType.DateTime)			};
            parameters[0].Value = OpenLabID;
            parameters[1].Value = date;
            SqlDataReader dr = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            dr.Read();
            Model.OpenLabStatus m = new Model.OpenLabStatus();
            m.OpenLabID = int.Parse(dr[0].ToString());
            m.date = Convert.ToDateTime(dr[1]);
            if (dr[2].ToString() != "")
            {
                m.lesson12 = int.Parse(dr[2].ToString());
            }
            if (dr[3].ToString() != "")
            {
            m.lesson34 = int.Parse(dr[3].ToString());
            }
            if (dr[4].ToString() != "")
            {
            m.lesson56 = int.Parse(dr[4].ToString());
            }
            if (dr[5].ToString() != "")
            {
            m.lesson78 = int.Parse(dr[5].ToString());
            }
            dr.Close();
            return m;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static bool Add(Model.OpenLabStatus model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into OpenLabStatus(");
            strSql.Append("OpenLabID,date,lesson12,lesson34,lesson56,lesson78)");
            strSql.Append(" values (");
            strSql.Append("@OpenLabID,@date,@lesson12,@lesson34,@lesson56,@lesson78)");
            SqlParameter[] parameters = {
					new SqlParameter("@OpenLabID", SqlDbType.Int,4),
					new SqlParameter("@date", SqlDbType.DateTime),
					new SqlParameter("@lesson12", SqlDbType.Int,4),
					new SqlParameter("@lesson34", SqlDbType.Int,4),
					new SqlParameter("@lesson56", SqlDbType.Int,4),
					new SqlParameter("@lesson78", SqlDbType.Int,4)};
            parameters[0].Value = model.OpenLabID;
            parameters[1].Value = model.date;
            parameters[2].Value = model.lesson12;
            parameters[3].Value = model.lesson34;
            parameters[4].Value = model.lesson56;
            parameters[5].Value = model.lesson78;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static bool Update(Model.OpenLabStatus model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update OpenLabStatus set ");
            strSql.Append("lesson12=@lesson12,");
            strSql.Append("lesson34=@lesson34,");
            strSql.Append("lesson56=@lesson56,");
            strSql.Append("lesson78=@lesson78");
            strSql.Append(" where OpenLabID=@OpenLabID and date=@date ");
            SqlParameter[] parameters = {
					new SqlParameter("@lesson12", SqlDbType.Int,4),
					new SqlParameter("@lesson34", SqlDbType.Int,4),
					new SqlParameter("@lesson56", SqlDbType.Int,4),
					new SqlParameter("@lesson78", SqlDbType.Int,4),
					new SqlParameter("@OpenLabID", SqlDbType.Int,4),
					new SqlParameter("@date", SqlDbType.DateTime)};
            parameters[0].Value = model.lesson12;
            parameters[1].Value = model.lesson34;
            parameters[2].Value = model.lesson56;
            parameters[3].Value = model.lesson78;
            parameters[4].Value = model.OpenLabID;
            parameters[5].Value = model.date;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
